#-- coding: utf-8 --

'''
Author: Ozone777
Date: 2023年6月16日
Description: 统计人员补卡次数
'''

import openpyxl
import os

def unmerge_and_fill(ws):
    merged_ranges = list(ws.merged_cells.ranges)
    for merged_cell_range in merged_ranges:
        ws.unmerge_cells(merged_cell_range.coord)
        top_left_value = ws.cell(row=merged_cell_range.min_row, column=merged_cell_range.min_col).value
        for row in ws.iter_rows(min_row=merged_cell_range.min_row, max_row=merged_cell_range.max_row,
                                min_col=merged_cell_range.min_col, max_col=merged_cell_range.max_col):
            for cell in row:
                if not cell.value:
                    cell.value = top_left_value


def count_names(ws):
    name_counts = {}
    for row in ws.iter_rows(min_row=2):
        name = row[0].value
        if name in name_counts:
            name_counts[name] += 1
        else:
            name_counts[name] = 1
    return name_counts


def write_result(name_counts, output_file):
    # 将数据写入新的 Excel 文件
    output_dir = "./output"
    os.makedirs(output_dir, exist_ok=True)  # 如果目录不存在，就创建目录
    output_path = os.path.join(output_dir, output_file)
    
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "姓名统计"
    ws.append(["姓名", "补卡次数"])
    for name, count in name_counts.items():
        ws.append([name, count])
    wb.save(output_path)


def main():
    input_file = "buka.xlsx"
    output_file = "buka-count.xlsx"

    wb = openpyxl.load_workbook(input_file)
    ws = wb.active

    unmerge_and_fill(ws)
    name_counts = count_names(ws)
    write_result(name_counts, output_file)

    print(f"处理完成, 结果已保存到文件: {output_file}")


if __name__ == "__main__":
    main()
